/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlserver;

import java.io.*;
import java.util.*;
import java.sql.Statement;

/**
 *
 * @author ROMANCE
 */
public class Select {    
  
  public static final String SELECT = "select";

  private static final String QUOTE_ESCAPE = "''";
  private static final String COMMA_ESCAPE = "~#####1~";


  private ArrayList binaryStreamObjectList = new ArrayList();

  public String tableName;

  public String whereStatement;

  public String sqlType;

  public String[] columnNames;

  public String[] columnValues;

  public String[] columnWhereNames;

  public String[] columnWhereValues;

  /**
   *Gets the tableName attribute of the SqlParser object
   *
   * @return    The tableName value
   * @since
   */
  public String getTableName() {
    return tableName;
  }

  /**
   * Gets the columnNames attribute of the SqlParser object
   *
   * @return    The columnNames value
   * @since
   */
  public String[] getColumnNames() {
    return columnNames;
  }

  public String[] getWhereColumnNames() {
    return columnWhereNames;
  }

  public String[] getWhereColumnValues() {
    return columnWhereValues;
  }

  public String[] getColumnValues() {
    return columnValues;
  }

  /**
   * Parse sql statement.
   *
   * @param sql defines SQL statement
   * @exception  Exception  Description of Exception
   * @since
   */
  public void parse(String sql) throws Exception {
    sql = sql.trim();
    tableName = null;
    columnNames = new String[0];
    columnValues = new String[0];
    columnWhereNames = new String[0];
    columnWhereValues = new String[0];
    whereStatement = null;
    sqlType = null;
    sql = sql.trim();

//replace comma(,) in values between quotes(')
    StringTokenizer tokQuote = new StringTokenizer(sql.toString(), "'", true);
    StringBuffer sb = new StringBuffer();
    boolean openParent1 = false;
    while (tokQuote.hasMoreTokens()) {
      String next = tokQuote.nextToken();
      if (openParent1)
        next = Utils.replaceAll(next, ",", COMMA_ESCAPE);
      sb.append(next);
      if (next.equalsIgnoreCase("'")) {
        if (openParent1 == true)
          openParent1 = false;
        else
          openParent1 = true;
      }
    }
//END replacement
    sql = sb.toString();
    String upperSql = sql.toUpperCase();


//handle unsupported statements
    if (upperSql.startsWith("ALTER "))
      throw new Exception("ALTER TABLE statements are not supported.");
    if (upperSql.startsWith("DROP "))
      throw new Exception("DROP statements are not supported.");

//SELECT
    if ( upperSql.startsWith("SELECT ") ) {
      if (upperSql.lastIndexOf(" FROM ") == -1) {
        throw new Exception("Malformed SQL. Missing FROM statement.");
      }

      sqlType = SELECT;
      int fromPos = upperSql.lastIndexOf(" FROM ");
      int wherePos = upperSql.lastIndexOf(" WHERE ");
      if (wherePos == -1)
        tableName = sql.substring(fromPos + 6).trim();
      else
        tableName = sql.substring(fromPos + 6, wherePos).trim();

      Vector cols = new Vector();
      StringTokenizer tokenizer = new StringTokenizer(upperSql.substring(7,
          fromPos), ",");

      while (tokenizer.hasMoreTokens()) {
        cols.add(tokenizer.nextToken().trim());
      }

      columnNames = new String[cols.size()];
      cols.copyInto(columnNames);
      if (wherePos != -1) {
        String strWhere = sql.substring(wherePos + 7);
        Vector whereCols = new Vector();
        Vector whereValues = new Vector();
        StringTokenizer tokenizerWhere = new StringTokenizer(strWhere, ",");

        while (tokenizerWhere.hasMoreTokens()) {
          String strToken = tokenizerWhere.nextToken();
          if (strToken.toLowerCase().indexOf(" and ") != -1) {
            String temp = strToken;
            int andPos = 0;
            out:
            do {
              andPos = temp.toLowerCase().indexOf(" and ");
              String strTokenAdd;
              if (andPos != -1)
                strTokenAdd = temp.substring(0, andPos).trim();
              else
                strTokenAdd = temp.trim();
              int delimiter2 = strTokenAdd.indexOf("=");
              if (delimiter2 != -1) {
                String valueAdd = strTokenAdd.substring(delimiter2 + 1).trim();
                valueAdd = Utils.handleQuotedString(valueAdd);
                whereCols.add(strTokenAdd.substring(0, delimiter2).trim());
                valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                whereValues.add(valueAdd);
              }
              else {
                int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                whereValues.add(null);
              }
              temp = temp.substring(andPos + 5);
              if (temp.toLowerCase().indexOf(" and ") == -1) {
                strTokenAdd = temp.trim();
                int delimiter4 = strTokenAdd.indexOf("=");
                if (delimiter4 != -1) {
                  String valueAdd = strTokenAdd.substring(delimiter4 + 1).trim();
                  valueAdd = Utils.handleQuotedString(valueAdd);
                  whereCols.add(strTokenAdd.substring(0, delimiter4).trim());
                  valueAdd = Utils.replaceAll(valueAdd, COMMA_ESCAPE, ",");
                  valueAdd = Utils.replaceAll(valueAdd, QUOTE_ESCAPE, "'");
                  whereValues.add(valueAdd);
                }
                else {
                  int delimiter3 = strTokenAdd.toLowerCase().indexOf(" is ");
                  whereCols.add(strTokenAdd.substring(0, delimiter3).trim());
                  whereValues.add(null);
                }
                break out;
              }

            }
            while (true);

          }
          else {
            int delimiter = strToken.indexOf("=");
            if (delimiter != -1) {
              String value = strToken.substring(delimiter + 1).trim();
              value = Utils.handleQuotedString(value);
              whereCols.add(strToken.substring(0, delimiter).trim());
              value = Utils.replaceAll(value, COMMA_ESCAPE, ",");
              value = Utils.replaceAll(value, QUOTE_ESCAPE, "'");
              whereValues.add(value);
            }
            else {
              int delimiter1 = strToken.toLowerCase().indexOf(" is ");
              whereCols.add(strToken.substring(0, delimiter1).trim());
              whereValues.add(null);
            }
          }
        }
        columnWhereNames = new String[whereCols.size()];
        columnWhereValues = new String[whereValues.size()];
        whereCols.copyInto(columnWhereNames);
        whereValues.copyInto(columnWhereValues);
      }
    }


  }
    
}
